package com.senior.english.servicesImp;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import com.senior.english.model.UserModel;

//@Service("userService")
//@Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
public class UserService {
	private JdbcTemplate jdbcTemplate;

	/**
	 * @return the jdbcTemplate
	 */
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	/**
	 * @param jdbcTemplate
	 *            the jdbcTemplate to set
	 */
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	/*
	 * check login
	 */
	public boolean checkLogin(String username, String password)
			throws SQLException {
		boolean flag = false;
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate())
				.withProcedureName("spd_checkLogin");
		MapSqlParameterSource mp = new MapSqlParameterSource();
		mp.addValue("usr", username);
		mp.addValue("pwd", password);
		// SqlParameterSource in = mp;
		Map<String, Object> m = sjc.execute(mp);
		try {
			if (!m.get("out_usr").toString().isEmpty())
				return flag = true;
		} catch (Exception ex) {

		}
		return flag;
	}

	/*
	 * list all user
	 */
	@SuppressWarnings({ "deprecation", "unchecked" })
	public List<UserModel> listUser() {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate())
				.withProcedureName("spd_listUser").returningResultSet(
						"users",
						ParameterizedBeanPropertyRowMapper
								.newInstance(UserModel.class));
		Map<String, Object> m = sjc.execute(new HashMap<String, Object>(0));
		List<UserModel> listUser = (List<UserModel>) m.get("users");
		return listUser;
	}

	/*
	 * get user by user_id
	 */
	@SuppressWarnings({ "deprecation", "unchecked" })
	public UserModel getUserById(int id) {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate())
				.withProcedureName("spd_getUserById").returningResultSet(
						"users",
						ParameterizedBeanPropertyRowMapper
								.newInstance(UserModel.class));
		MapSqlParameterSource mp = new MapSqlParameterSource();
		mp.addValue("id", id);
		Map<String, Object> m = sjc.execute(mp);
		List<UserModel> user = (List<UserModel>) m.get("users");
		return user.get(0);
	}

	/*
	 * update user by user_id
	 */

	public void updateUser(int id, String usr, String pwd) {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate())
				.withProcedureName("spd_updateUser");
		MapSqlParameterSource mp = new MapSqlParameterSource();
		mp.addValue("id", id);
		mp.addValue("usr", usr);
		mp.addValue("pwd", pwd);
		Map<String, Object> m = sjc.execute(mp);
	}
}